﻿using System;
using System.Data;
using System.Text;
using System.Data.SQLite;

namespace Clipper.DAL
{
    /// <summary>
    /// 数据访问类:DetectItems
    /// </summary>
    public partial class DetectItems
    {
        public DetectItems()
        { }
        ResultReference mResultDal = new ResultReference();
        QCReference mQCDal = new QCReference();
        DetectVariety mDetectVarietyDal = new DetectVariety();
        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string detectItem)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from DetectItems");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@detectItem", DbType.String,20)			};
            parameters[0].Value = detectItem;

            return SQLiteHelper.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Clipper.Model.DetectItems model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into DetectItems(");
            strSql.Append("detectItem,name,DetectVarietyID,bloodAmount,reagentName1,reagentAmount1,reagentName2,reagentAmount2,reagentName3,reagentAmount3,incubationTime,incubationTime1,incubationTime2,detectTime,standardAmount,FittingApproachID,correlationThreshold,CoordinateID,standardState,StandardInfoID,note)");
            strSql.Append(" values (");
            strSql.Append("@detectItem,@name,@DetectVarietyID,@bloodAmount,@reagentName1,@reagentAmount1,@reagentName2,@reagentAmount2,@reagentName3,@reagentAmount3,@incubationTime,@incubationTime1,@incubationTime2,@detectTime,@standardAmount,@FittingApproachID,@correlationThreshold,@CoordinateID,@standardState,@StandardInfoID,@note)");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@detectItem", DbType.String,20),
					new SQLiteParameter("@name", DbType.String,50),
					new SQLiteParameter("@DetectVarietyID", DbType.Int32,4),
					new SQLiteParameter("@bloodAmount", DbType.Int16,1),
                    new SQLiteParameter("@reagentName1",  DbType.String,50),
                    new SQLiteParameter("@reagentAmount1", DbType.Int16,1),
                    new SQLiteParameter("@reagentName2",  DbType.String,50),
                    new SQLiteParameter("@reagentAmount2", DbType.Int16,1),
                    new SQLiteParameter("@reagentName3",  DbType.String,50),
                    new SQLiteParameter("@reagentAmount3",DbType.Int16,1),
					new SQLiteParameter("@incubationTime", DbType.Int16,1),
                    new SQLiteParameter("@incubationTime1", DbType.Int16,1),
                    new SQLiteParameter("@incubationTime2", DbType.Int16,1),
					new SQLiteParameter("@detectTime", DbType.Int16,1),
					new SQLiteParameter("@standardAmount", DbType.Int16,1),
					new SQLiteParameter("@FittingApproachID", DbType.Int32,4),
					new SQLiteParameter("@correlationThreshold", DbType.Decimal,8),
					new SQLiteParameter("@CoordinateID", DbType.Int16,1),
					new SQLiteParameter("@standardState", DbType.Int16,1),
					new SQLiteParameter("@StandardInfoID", DbType.Int32,4),
					new SQLiteParameter("@note", DbType.String)};
            parameters[0].Value = model.detectItem;
            parameters[1].Value = model.name;
            parameters[2].Value = model.DetectVarietyID;
            parameters[3].Value = model.bloodAmount;
            parameters[4].Value = model.reagentName1;
            parameters[5].Value = model.reagentAmount1;
            parameters[6].Value = model.reagentName2;
            parameters[7].Value = model.reagentAmount2;
            parameters[8].Value = model.reagentName3;
            parameters[9].Value = model.reagentAmount3;
            parameters[10].Value = model.incubationTime;
            parameters[11].Value = model.incubationTime1;
            parameters[12].Value = model.incubationTime2;
            parameters[13].Value = model.detectTime;
            parameters[14].Value = model.standardAmount;
            parameters[15].Value = model.FittingApproachID;
            parameters[16].Value = model.correlationThreshold;
            parameters[17].Value = model.CoordinateID;
            parameters[18].Value = model.standardState;
            parameters[19].Value = model.StandardInfoID;
            parameters[20].Value = model.note;
            
            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Clipper.Model.DetectItems model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update DetectItems set ");
            strSql.Append("name=@name,");
            strSql.Append("DetectVarietyID=@DetectVarietyID,");
            strSql.Append("bloodAmount=@bloodAmount,");
            strSql.Append("reagentName1=@reagentName1,");
            strSql.Append("reagentAmount1=@reagentAmount1,");
            strSql.Append("reagentName2=@reagentName2,");
            strSql.Append("reagentAmount2=@reagentAmount2,");
            strSql.Append("reagentName3=@reagentName3,");
            strSql.Append("incubationTime=@incubationTime,");
            strSql.Append("detectTime=@detectTime,");
            strSql.Append("standardAmount=@standardAmount,");
            strSql.Append("FittingApproachID=@FittingApproachID,");
            strSql.Append("correlationThreshold=@correlationThreshold,");
            strSql.Append("CoordinateID=@CoordinateID,");
            strSql.Append("standardState=@standardState,");
            strSql.Append("StandardInfoID=@StandardInfoID,");
            strSql.Append("reagentAmount3=@reagentAmount3,");
            strSql.Append("incubationTime1=@incubationTime1,");
            strSql.Append("incubationTime2=@incubationTime2,");
            strSql.Append("note=@note");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@name", DbType.String,50),
					new SQLiteParameter("@DetectVarietyID", DbType.Int32,4),
					new SQLiteParameter("@bloodAmount", DbType.Int16,1),
                    new SQLiteParameter("@reagentName1", DbType.String,50),
                    new SQLiteParameter("@reagentAmount1", DbType.Int16,1),
                    new SQLiteParameter("@reagentName2", DbType.String,50),
                    new SQLiteParameter("@reagentAmount2", DbType.Int16,1),
                    new SQLiteParameter("@reagentName3", DbType.String,50),
                    new SQLiteParameter("@incubationTime", DbType.Int16,1),
					new SQLiteParameter("@detectTime", DbType.Int16,1),
					new SQLiteParameter("@standardAmount", DbType.Int16,1),
					new SQLiteParameter("@FittingApproachID", DbType.Int32,4),
					new SQLiteParameter("@correlationThreshold", DbType.Decimal,8),
					new SQLiteParameter("@CoordinateID", DbType.Int16,1),
					new SQLiteParameter("@standardState", DbType.Int16,1),
					new SQLiteParameter("@StandardInfoID", DbType.Int32,4),
                    new SQLiteParameter("@reagentAmount3",DbType.Int16,1),
					new SQLiteParameter("@incubationTime1", DbType.Int16,1),
					new SQLiteParameter("@incubationTime2", DbType.Int16,1),
					new SQLiteParameter("@note", DbType.String),
					new SQLiteParameter("@detectItem", DbType.String,20)};
            parameters[0].Value = model.name;
            parameters[1].Value = model.DetectVarietyID;
            parameters[2].Value = model.bloodAmount;
            parameters[3].Value = model.reagentName1;
            parameters[4].Value = model.reagentAmount1;
            parameters[5].Value = model.reagentName2;
            parameters[6].Value = model.reagentAmount2;
            parameters[7].Value = model.reagentName3;
            parameters[8].Value = model.incubationTime;
            parameters[9].Value = model.detectTime;
            parameters[10].Value = model.standardAmount;
            parameters[11].Value = model.FittingApproachID;
            parameters[12].Value = model.correlationThreshold;
            parameters[13].Value = model.CoordinateID;
            parameters[14].Value = model.standardState;
            parameters[15].Value = model.StandardInfoID;
            parameters[16].Value = model.reagentAmount3;
            parameters[17].Value = model.incubationTime1;
            parameters[18].Value = model.incubationTime2;
            parameters[19].Value = model.note;
            parameters[20].Value = model.detectItem;

            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string detectItem)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from DetectItems ");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@detectItem", DbType.String,20)			};
            parameters[0].Value = detectItem;

            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string detectItemlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from DetectItems ");
            strSql.Append(" where detectItem in (" + detectItemlist + ")  ");
            int rows = SQLiteHelper.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Clipper.Model.DetectItems GetModel(string detectItem)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detectItem,name,DetectVarietyID,bloodAmount,reagentName1,reagentAmount1,reagentName2,reagentAmount2,reagentName3,reagentAmount3,incubationTime,incubationTime1,incubationTime2,detectTime,standardAmount,FittingApproachID,correlationThreshold,CoordinateID,standardState,StandardInfoID,note from DetectItems ");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@detectItem", DbType.String,20)			};
            parameters[0].Value = detectItem;

            Clipper.Model.DetectItems model = new Clipper.Model.DetectItems();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Clipper.Model.DetectItems DataRowToModel(DataRow row)
        {
            Clipper.Model.DetectItems model = new Clipper.Model.DetectItems();
            if (row != null)
            {
                if (row["detectItem"] != null)
                {
                    model.detectItem = row["detectItem"].ToString();
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["DetectVarietyID"] != null && row["DetectVarietyID"].ToString() != "")
                {
                    model.DetectVarietyID = int.Parse(row["DetectVarietyID"].ToString());
                }
                if (row["bloodAmount"] != null && row["bloodAmount"].ToString() != "")
                {
                    model.bloodAmount = int.Parse(row["bloodAmount"].ToString());
                }
                //设置名字1
                if(row["reagentName1"]!=null)
                {
                    model.reagentName1= row["reagentName1"].ToString();
                }
                //设置名字2
                if (row["reagentName2"] != null)
                {
                    model.reagentName2 = row["reagentName2"].ToString();
                }
                //设置名字3
                if (row["reagentName3"] != null )
                {
                    model.reagentName3 = row["reagentName3"].ToString();
                }
                if (row["reagentAmount1"] != null && row["reagentAmount1"].ToString() != "")
                {
                    model.reagentAmount1 = byte.Parse(row["reagentAmount1"].ToString());
                }
                if (row["reagentAmount2"] != null && row["reagentAmount2"].ToString() != "")
                {
                    model.reagentAmount2 = byte.Parse(row["reagentAmount2"].ToString());
                }
                if (row["reagentAmount3"] != null && row["reagentAmount3"].ToString() != "")
                {
                    model.reagentAmount3 = byte.Parse(row["reagentAmount3"].ToString());
                }
                if (row["incubationTime"] != null && row["incubationTime"].ToString() != "")
                {
                    model.incubationTime = byte.Parse(row["incubationTime"].ToString());
                }
                if (row["incubationTime1"] != null && row["incubationTime1"].ToString() != "")
                {
                    model.incubationTime1 = byte.Parse(row["incubationTime1"].ToString());
                }
                if (row["incubationTime2"] != null && row["incubationTime2"].ToString() != "")
                {
                    model.incubationTime2 = byte.Parse(row["incubationTime2"].ToString());
                }
                if (row["detectTime"] != null && row["detectTime"].ToString() != "")
                {
                    model.detectTime = int.Parse(row["detectTime"].ToString());
                }
                if (row["standardAmount"] != null && row["standardAmount"].ToString() != "")
                {
                    model.standardAmount = int.Parse(row["standardAmount"].ToString());
                }
                if (row["FittingApproachID"] != null && row["FittingApproachID"].ToString() != "")
                {
                    model.FittingApproachID = int.Parse(row["FittingApproachID"].ToString());
                }
                if (row["correlationThreshold"] != null && row["correlationThreshold"].ToString() != "")
                {
                    model.correlationThreshold = decimal.Parse(row["correlationThreshold"].ToString());
                }
                if (row["CoordinateID"] != null && row["CoordinateID"].ToString() != "")
                {
                    model.CoordinateID = int.Parse(row["CoordinateID"].ToString());
                }
                if (row["standardState"] != null && row["standardState"].ToString() != "")
                {
                    model.standardState = byte.Parse(row["standardState"].ToString());
                }
                if (row["StandardInfoID"] != null && row["StandardInfoID"].ToString() != "")
                {
                    model.StandardInfoID = int.Parse(row["StandardInfoID"].ToString());
                }
                if (row["note"] != null)
                {
                    model.note = row["note"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detectItem,name,DetectVarietyID,bloodAmount,reagentName1,reagentAmount1,reagentName2,reagentAmount2,reagentName3,reagentAmount3,incubationTime,incubationTime1,incubationTime2,detectTime,standardAmount,FittingApproachID,correlationThreshold,CoordinateID,standardState,StandardInfoID,note ");
            strSql.Append(" FROM DetectItems ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLiteHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM DetectItems ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = SQLiteHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.detectItem desc");
            }
            strSql.Append(")AS Row, T.*  from DetectItems T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return SQLiteHelper.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@tblName", DbType.VarChar, 255),
                    new SQLiteParameter("@fldName", DbType.VarChar, 255),
                    new SQLiteParameter("@PageSize", DbType.Int32),
                    new SQLiteParameter("@PageIndex", DbType.Int32),
                    new SQLiteParameter("@IsReCount", DbType.bit),
                    new SQLiteParameter("@OrderType", DbType.bit),
                    new SQLiteParameter("@strWhere", DbType.VarChar,1000),
                    };
            parameters[0].Value = "DetectItems";
            parameters[1].Value = "detectItem";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return SQLiteHelper.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 获得可选择检测项的数据列表
        /// </summary>
        public DataSet GetSelectList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detectItem,name,DetectVarietyID,bloodAmount,reagentName1,reagentAmount1,reagentName2,reagentAmount2,reagentName3,reagentAmount3,incubationTime,incubationTime1,incubationTime2,detectTime,standardAmount,FittingApproachID,correlationThreshold,CoordinateID,standardState,StandardInfoID,note  ");
            strSql.Append(" FROM DetectItems,detectvariety where detectVarietyId=itemtypeId and isshow=1 order by ordernumber");
            return SQLiteHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 导出检测项数据(检测项、结果对照、质控对照、类别信息)
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="password"></param>
        public bool ExportExcel(string fileName, string password,string version)
        {
            bool res = false;
            DataSet dataSet = new DataSet();

            DataTable dtItem = GetList("").Tables[0];
            dtItem.TableName = "DetectItems";
            DataTable dtResult = mResultDal.GetList("").Tables[0];
            dtResult.TableName = "ResultReference";
            DataTable dtQC = mQCDal.GetList("").Tables[0];
            dtQC.TableName = "QCReference";
            DataTable dtVariety = mDetectVarietyDal.GetExportList("").Tables[0];
            dtVariety.TableName = "DetectVariety";
            //SQLiteHelper.CreateDatabase(fileName, password);
            //SQLiteHelper.ExportData(dtItem, fileName, password);
            //SQLiteHelper.ExportData(dtResult, fileName, password);
            //SQLiteHelper.ExportData(dtQC, fileName, password);

            dataSet.Tables.Add(VersionTable(version));
            dataSet.Tables.Add(dtItem.Copy());
            dataSet.Tables.Add(dtResult.Copy());
            dataSet.Tables.Add(dtQC.Copy());
            dataSet.Tables.Add(dtVariety.Copy());

            ExcelHelper.DataSetToExcel(dataSet, fileName, password, password);
            res = true;
            return res;
        }
        /// <summary>
        /// 导入检测项数据(检测项、结果对照、质控对照、类别信息)
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="password"></param>
        public bool ImportExcel(string fileName,string password)
        {
            //SQLiteHelper.ImportData(fileName, "DetectItems", password);
            //SQLiteHelper.ImportData(fileName, "ResultReference", password);
            //SQLiteHelper.ImportData(fileName, "QCReference", password);
            DataSet dt= ExcelHelper.DataSetFromExcel(fileName, password, password);
            SQLiteHelper.ImportUpdateData(dt.Tables["DetectVariety"]);
            dt.Tables.Remove("DetectVariety");
            return  SQLiteHelper.ImportData(dt);
        }
        /// <summary>
        /// 添加数据库版本信息，放在一个sheet中
        /// </summary>
        /// <param name="version"></param>
        /// <returns></returns>
        public DataTable VersionTable(string version)
        {
            DataTable dt = new DataTable("Version");
            dt.Columns.Add(new DataColumn(version));
            return dt;
        }

        #endregion  ExtensionMethod
    }
}

